﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Windows.Forms;
using System.Xml;

namespace CollectSQLServerInfo
{
    public partial class MainForm : Form
    {
        const string CollectionDb = "SQLCollection";
        const string CollectionServer = "4YFNRW1";

        private Guid _run;

        public MainForm()
        {
            InitializeComponent();

        }

        private void btnCollect_Click(object sender, EventArgs e)
        {
            File.Delete("log.txt");
            _run = Guid.NewGuid();
            StampTheRun(true);
            labelCurrentServer.Text = "";
            labelCurrentScript.Text = "";
            if (ToggleTruncate.Checked)
            {
                TruncateTablesInCollectionDb();
                ToggleTruncate.Checked = false;
            }

            ListBox.SelectedObjectCollection servers = listSQLServers.SelectedItems;

            foreach (DataRowView item in listSQLServers.SelectedItems)
            {
                CollectFromServer(item[0].ToString());
            }

            StampTheRun(false);
            labelCurrentServer.Text = @"Completed";
            labelCurrentScript.Text = @"Completed";
        }

        //execute the file and save results to XML file
        private void CollectFromServer(string server)
        {
            try
            {
                using (var conn = new SqlConnection())
                {
                    LogWrite(string.Format("Starting server: {0}", server));
                    conn.ConnectionString = string.Format("server={0};Trusted_Connection=yes;database=master;", server);
                    conn.Open();
                    foreach (string script in Directory.GetFiles(@".\Scripts", "*.xml"))
                    {
                        labelCurrentScript.Text = script;
                        var xmlDoc = new XmlDocument();
                        xmlDoc.Load(script);

                        //test for table existance
                        EnsureTableExists(xmlDoc.SelectSingleNode("root/table"));

                        //execute the query and get back the DS
                        XmlNode selectSingleNode = xmlDoc.SelectSingleNode("root/sql");
                        if (selectSingleNode != null)
                        {
                            string sql = selectSingleNode.FirstChild.Value;
                            DataSet ds = ExecuteScript(conn, sql);

                            //write the datatables to the collection DB
                            if (ds != null)
                            {
                                InsertDataSet(xmlDoc.SelectSingleNode("root/table"), ds);
                            }
                        }
                    }
                    conn.Close();
                    labelCurrentScript.Text = @"Completed";
                }
            }
            catch (Exception ex)
            {
                // MessageBox.Show(ex.Message);
                LogWrite(string.Format("{0} | {1} | {2}", labelCurrentServer.Text, labelCurrentScript.Text, ex.Message));
            }
        }

        private void EnsureTableExists(XmlNode table)
        {
            using (var collectionConn = new SqlConnection(collectionConnString()))
            {
                collectionConn.Open();
                if (table.Attributes != null)
                {
                    string tableName = table.Attributes["name"].Value;
                    string sql = string.Format("if (object_id('{0}') is null) create table {0} (", tableName);

                    sql = table.SelectNodes("column").Cast<XmlNode>().Aggregate(sql, (current, col) => current + String.Format("{0} {1} null, ", col.Attributes["name"].Value, col.Attributes["datatype"].Value));

                    sql += "RowNumber int identity(0,1) not null); ";
                    using (var collectionCmd = new SqlCommand(sql, collectionConn))
                    {
                        collectionCmd.ExecuteNonQuery();
                    }
                }
                collectionConn.Close();
            }
        }

        private void TruncateTablesInCollectionDb()
        {
            using (var collectionConn = new SqlConnection(collectionConnString()))
            {
                collectionConn.Open();

                const string sql = "declare @sql varchar(2000), @tableName varchar(50); declare tables cursor fast_forward for select [name] from sys.objects where type = 'u' and name like 'tk_%'; open tables; fetch next from tables into @tableName; while @@FETCH_STATUS = 0 begin set @sql = 'truncate table ' + @tableName print @sql exec (@sql) fetch next from tables into @tableName end; close tables; deallocate tables;";
                using (var collectionCmd = new SqlCommand(sql, collectionConn))
                {
                    collectionCmd.ExecuteNonQuery();
                }
                collectionConn.Close();
            }
        }

        private void InsertDataSet(XmlNode table, DataSet ds)
        {
            try
            {
                using (var collectionConn = new SqlConnection(collectionConnString()))
                {
                    collectionConn.Open();
                    using (var bulkCopy = new SqlBulkCopy(collectionConn))
                    {
                        if (table.Attributes != null) bulkCopy.DestinationTableName = table.Attributes["name"].Value;
                        foreach (DataTable dt in ds.Tables)
                        {
                            var guid = new DataColumn("GUID") { DefaultValue = _run.ToString() };
                            dt.Columns.Add(guid);
                            //MessageBox.Show(dt.Rows.Count.ToString());
                            bulkCopy.WriteToServer(dt);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogWrite(string.Format("{0} | {1} | {2}", labelCurrentServer.Text, labelCurrentScript.Text, ex.Message));
            }
        }

        private void StampTheRun(bool begining)
        {
            using (var collectionConn = new SqlConnection(collectionConnString()))
            {
                collectionConn.Open();
                string sql = null;
                if (begining)
                {
                    sql = String.Format("INSERT INTO [dbo].[Collections] ([CollectionStart] ,[CollectionGuid])  VALUES  (getdate(),'{0}')", _run);
                }
                else
                {
                    sql = String.Format("update [dbo].[Collections] set [CollectionEnd] = getdate() where CollectionGuid = '{0}'",_run);
                }
                SqlCommand cmd = new SqlCommand(sql, collectionConn);
                cmd.ExecuteNonQuery();
                collectionConn.Close();


            }
        }

        private DataSet ExecuteScript(SqlConnection conn, string script)
        {
            try
            {
                using (var ds = new DataSet())
                {
                    using (var cmd = new SqlCommand(script, conn))
                    {
                        using (var da = new SqlDataAdapter())
                        {
                            da.SelectCommand = cmd;
                            da.Fill(ds);
                        }
                    }
                    return ds;
                }
            }
            catch (Exception ex)
            {
                LogWrite(string.Format("{0} | {1} | {2}", labelCurrentServer.Text, labelCurrentScript.Text, ex.Message));
                return null;
            }
        }

        private string collectionConnString()
        {
            return String.Format("server={0};trusted_connection=yes;database={1};", CollectionServer, CollectionDb);
        }

        private static void LogWrite(string logMessage)
        {
            try
            {
                using (StreamWriter w = File.AppendText("log.txt"))
                {
                    Log(logMessage, w);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private static void Log(string logMessage, TextWriter txtWriter)
        {
            try
            {
                txtWriter.Write("\r\nLog Entry : {0} : ,{1}", DateTime.Now, logMessage);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void MainForm_Load(object sender, EventArgs e)
        {
            string sql =
                "SELECT [ServerName] FROM [SQLCollection].[dbo].[SqlServers] order by [ServerName]";
            var conn = new SqlConnection(collectionConnString());
            DataSet servers = ExecuteScript(conn, sql);
            listSQLServers.DataSource = servers.Tables[0];

            listSQLServers.DisplayMember = "ServerName";
            listSQLServers.ValueMember = "ServerName";

            string[] args = Environment.GetCommandLineArgs();
            if (args.Length > 0)
            {
                if (args.Contains("auto"))
                {
                    for (int i = 0; i < listSQLServers.Items.Count; i++)
                    {
                        listSQLServers.SetSelected(i, true);
                    }

                    btnCollect_Click(null, null);
                    this.Close();
                }
            }
        }
    }
}